ITHACK
【MySQL】FOREIGN KEY REFERENCES制約 - 外部キー（他テーブルの値）

【MySQL】FOREIGN KEY REFERENCES制約 - 外部キー（他テーブルの値）

MySQLのFOREIGN KEY制約について解説します。

検証環境

外部キー

外部キーは“別カラムを参照するカラム（キー）”です。

任意のカラムに設定でき、参照先のカラムに存在する値のみ格納できます。

FOREIGN KEY制約

FOREIGN KEY制約は“外部キーを設定する制約”です。

外部キー制約によって、参照先カラムに存在する値のみ保持することができます。

また、参照データを変更・削除した場合の連動した処理も設定可能です。

定義

FOREIGN KEY制約はテーブル定義に追加して利用します。

基本構文

CREATE TABLE テーブル名 (
    カラム定義
    FOREIGN KEY ( カラム名, ... ) REFERENCES 参照先テーブル名( 参照先カラム名, ... )
);

FOREIGN KEY ( カラム名, ... ) REFERENCES 参照先テーブル名( 参照先カラム名, ... )の部分がFOREIGN KEY制約の記述です。

FOREIGN KEY ( カラム名, ... )の丸括弧（()）内に外部キーとするカラム、参照先テーブル名( 参照先カラム名, ... )の丸括弧（()）内に参照先のカラムを定義し、複数ある場合はカンマ（,）で区切ります。

サンプル

mysql> /* ショップテーブル */
mysql> CREATE TABLE shops (
    ->     id INT PRIMARY KEY AUTO_INCREMENT,
    ->     name VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO shops ( name )
    -> VALUES ( 'Fruits Shop' ), ( 'Food Shop' ), ( 'Dessert Shop' );
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM shops;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | Fruits Shop  |
|  2 | Food Shop    |
|  3 | Dessert Shop |
+----+--------------+
3 rows in set (0.00 sec)



mysql> /* 商品テーブル */
mysql> CREATE TABLE items (
    ->     id INT PRIMARY KEY AUTO_INCREMENT,
    ->     name VARCHAR(20),
    ->     price INT,
    ->     stock INT,
    ->     shop_id INT,
    ___ih_hl_start
    ->     FOREIGN KEY ( shop_id ) REFERENCES shops(id)
    ___ih_hl_end
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO items ( name, price, stock, shop_id ) VALUES
    -> ( 'Apple', 200, 10, 1 ),
    -> ( 'Orange', 150, 3, 1 ),
    -> ( 'Pineapple', 1100, 3, 2 ),
    -> ( 'Grapes', 3500, 1, 3 );
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM items;
+----+-----------+-------+-------+---------+
| id | name      | price | stock | shop_id |
+----+-----------+-------+-------+---------+
|  1 | Apple     |   200 |    10 |       1 |
|  2 | Orange    |   150 |     3 |       1 |
|  3 | Pineapple |  1100 |     3 |       2 |
|  4 | Grapes    |  3500 |     1 |       3 |
+----+-----------+-------+-------+---------+
4 rows in set (0.00 sec)

mysql> INSERT INTO items ( name, price, stock, shop_id ) VALUE ( 'Apple', 200, 10, 4 );
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mydb`.`items`, CONSTRAINT `items_ibfk_1` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`))

ショップテーブルと商品テーブルを作成し、商品テーブルにショップテーブルの外部キーを設定しました。（３２行目）

最後のデータ追加（INSERT INTO VALUE）では、ショップテーブルに存在しない値を外部キーに設定したためエラーが発生しました。

確認

外部キーはSHOW CREATE TABLEで確認できます。

mysql> SHOW CREATE TABLE items;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                             |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| items | CREATE TABLE `items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `price` int DEFAULT NULL,
  `stock` int DEFAULT NULL,
  `shop_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `shop_id` (`shop_id`),
  CONSTRAINT `items_ibfk_1` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

参照データの更新・削除

初期設定では外部キーの参照データは変更・削除ができません。

例えば次のようなケースです。

mysql> SELECT * FROM shops;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | Fruits Shop  |
|  2 | Food Shop    |
|  3 | Dessert Shop |
+----+--------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM items;
+----+-----------+-------+-------+---------+
| id | name      | price | stock | shop_id |
+----+-----------+-------+-------+---------+
|  1 | Apple     |   200 |    10 |       1 |
|  2 | Orange    |   150 |     3 |       1 |
|  3 | Pineapple |  1100 |     3 |       2 |
+----+-----------+-------+-------+---------+
3 rows in set (0.00 sec)

mysql> UPDATE shops SET id = 99 WHERE id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE shops SET id = 99 WHERE id = 2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mydb`.`items`, CONSTRAINT `items_ibfk_1` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`))

shopsテーブルのidを変更するクエリを２回実行しました。

１回目（２１行目）はidが3のレコードに対して実行し、正常に処理されましたが、２回目（２５行目）はエラーが発生しています。

この違いは外部キーの参照データであるかどうかで、itemsテーブルでidが3のフィールドは参照されていませんが、2は参照されています。

この『エラーの発生』の処理は変更することができます。

更新時の処理

参照データが更新された場合の処理はON UPDATEで設定します。

基本構文

CREATE TABLE テーブル名 (
    カラム定義
    FOREIGN KEY ( カラム名, ... ) REFERENCES 参照先テーブル名( 参照先カラム名, ... ) ON UPDATE オプション
);

FOREIGN KEY制約の最後にON UPDATE オプションを追記します。

設定可能なオプションは主に次の３つです。

オプション 内容
RESTRICT エラーを発生します。初期値です。
CASCADE 外部キーの値も変更します。
SET NULL 外部キーの値をNULLに設定します。

サンプル

mysql> CREATE TABLE items (
    ->     id INT PRIMARY KEY AUTO_INCREMENT,
    ->     name VARCHAR(20),
    ->     price INT,
    ->     stock INT,
    ->     shop_id INT,
    ___ih_hl_start
    ->     FOREIGN KEY ( shop_id ) REFERENCES shops(id) ON UPDATE CASCADE
    ___ih_hl_end
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO items ( name, price, stock, shop_id ) VALUES
    -> ( 'Apple', 200, 10, 1 ),
    -> ( 'Orange', 150, 3, 1 ),
    -> ( 'Pineapple', 1100, 3, 2 );
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM shops;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | Fruits Shop  |
|  2 | Food Shop    |
|  3 | Dessert Shop |
+----+--------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM items;
+----+-----------+-------+-------+---------+
| id | name      | price | stock | shop_id |
+----+-----------+-------+-------+---------+
|  1 | Apple     |   200 |    10 |       1 |
|  2 | Orange    |   150 |     3 |       1 |
|  3 | Pineapple |  1100 |     3 |       2 |
+----+-----------+-------+-------+---------+
3 rows in set (0.00 sec)

mysql> UPDATE shops SET id = 99 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM shops;
+----+--------------+
| id | name         |
+----+--------------+
|  2 | Food Shop    |
|  3 | Dessert Shop |
| 99 | Fruits Shop  |
+----+--------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM items;
+----+-----------+-------+-------+---------+
| id | name      | price | stock | shop_id |
+----+-----------+-------+-------+---------+
|  1 | Apple     |   200 |    10 |      99 |
|  2 | Orange    |   150 |     3 |      99 |
|  3 | Pineapple |  1100 |     3 |       2 |
+----+-----------+-------+-------+---------+
3 rows in set (0.00 sec)

７行目のFOREIGN KEY制約で参照データが更新された場合の処理をCASCADEに設定しています。

３８行目でshopsテーブルの参照データを更新しましたが、CASCADEに従ってitemsテーブルの外部キーの値も更新されています。

削除時の処理

参照データが削除された場合の処理はON DELETEで設定します。

基本構文

CREATE TABLE テーブル名 (
    カラム定義
    FOREIGN KEY ( カラム名, ... ) REFERENCES 参照先テーブル名( 参照先カラム名, ... ) ON DELETE オプション
);

FOREIGN KEY制約の最後にON DELETE オプションを追記します。

設定可能なオプションは主に次の３つです。

オプション 内容
RESTRICT エラーを発生します。初期値です。
CASCADE 外部キーのレコードも削除します。
SET NULL 外部キーの値をNULLに設定します。

また、ON UPDATEと合わせて設定することも可能です。

FOREIGN KEY ( カラム名, ... ) REFERENCES 参照先テーブル名( 参照先カラム名, ... ) ON UPDATE オプション ON DELETE オプション

サンプル

mysql> CREATE TABLE items (
    ->     id INT PRIMARY KEY AUTO_INCREMENT,
    ->     name VARCHAR(20),
    ->     price INT,
    ->     stock INT,
    ->     shop_id INT,
    ___ih_hl_start
    ->     FOREIGN KEY ( shop_id ) REFERENCES shops(id) ON DELETE CASCADE
    ___ih_hl_end
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO items ( name, price, stock, shop_id ) VALUES
    -> ( 'Apple', 200, 10, 1 ),
    -> ( 'Orange', 150, 3, 1 ),
    -> ( 'Pineapple', 1100, 3, 2 );
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM shops;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | Fruits Shop  |
|  2 | Food Shop    |
|  3 | Dessert Shop |
+----+--------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM items;
+----+-----------+-------+-------+---------+
| id | name      | price | stock | shop_id |
+----+-----------+-------+-------+---------+
|  1 | Apple     |   200 |    10 |       1 |
|  2 | Orange    |   150 |     3 |       1 |
|  3 | Pineapple |  1100 |     3 |       2 |
+----+-----------+-------+-------+---------+
3 rows in set (0.00 sec)

mysql> DELETE FROM shops WHERE id = 1;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM shops;
+----+--------------+
| id | name         |
+----+--------------+
|  2 | Food Shop    |
|  3 | Dessert Shop |
+----+--------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM items;
+----+-----------+-------+-------+---------+
| id | name      | price | stock | shop_id |
+----+-----------+-------+-------+---------+
|  3 | Pineapple |  1100 |     3 |       2 |
+----+-----------+-------+-------+---------+
1 row in set (0.00 sec)

７行目のFOREIGN KEY制約で参照データが削除された場合の処理をCASCADEに設定しています。

３８行目でshopsテーブルの参照データを削除しており、CASCADEに従ってitemsテーブルの外部キーの値に削除した参照データを持つレコードが削除されています。

【CSS】word-break - 単語中の改行
【コマンドライン】ユーザーのグループ確認 [ groups ]
【コマンドライン】ユーザー・グループの確認 [ getent ]
【JavaScript】イベントの伝搬 - キャプチャリング・バブリング
【JavaScript / DOM】属性ノードの取得・設定・削除 - 属性ノードを要素ノードで扱う方法
【JavaScript / DOM】ノード / textContentプロパティ - ノードのテキストを変更する
AtMarkRule
Browser
CentOS
Command
Command
CSS
Database
Development
DOM
Event
HTML
JavaScript
MarkupLanguage
MySQL
Network
OS
PHP
Programming
Property
Pseudo Class
Pseudo Element
Relational Database
Server
Standard Object
Tag